--Local Projects Database 1.03 to 1.04 DB update script

--Confidential and Proprietary, Subject to the Non-Disclosure Agreement,
--Version 1.0, between the Development Gateway Foundation, Inc and the
--Recipient Copyright 2001-2004 Development Gateway Foundation, Inc.

-- Unauthorized Disclosure Prohibited

--LPD PostgreSQL DB update script
--LPD DB required version=1.03
--LPD DB current version=1.04

--!Backup the data first!


SET SESSION AUTHORIZATION 'lpd';
SET search_path = public, pg_catalog;

-----------------------------------------------
--creating search views:

CREATE OR REPLACE VIEW obj_counties AS
SELECT DISTINCT o.activity_id, loc.jud_id, cou.name
FROM counties cou, locations loc, objects o
WHERE cou.id = loc.jud_id AND o.id = loc.id AND o.obj_type = 23
ORDER BY o.activity_id, loc.jud_id;

CREATE OR REPLACE VIEW obj_countries AS
SELECT DISTINCT o.activity_id, loc.country_iso3, cnt.name
FROM countries_iso3 cnt, locations loc, objects o
WHERE cnt.iso3 = loc.country_iso3 AND o.id = loc.id AND o.obj_type = 23
ORDER BY o.activity_id, loc.country_iso3;

CREATE OR REPLACE VIEW obj_cregions AS
SELECT DISTINCT o.activity_id, cr.id, cr.name
FROM cregions cr, locations loc, objects o
WHERE cr.id = loc.cregion_id AND o.id = loc.id AND o.obj_type = 23
ORDER BY o.activity_id, cr.id;

CREATE OR REPLACE VIEW obj_executing_orgs AS
SELECT DISTINCT o.activity_id, org.id, org.name, org.lang
FROM orgs_desc org, orgs_inv oi, objects o
WHERE org.id = oi.org_id AND o.id = oi.id AND o.obj_type = 10 AND oi.role=101
ORDER BY o.activity_id, org.id;

CREATE OR REPLACE VIEW obj_funding_amount AS
SELECT DISTINCT o.activity_id, sum(f.amount) AS amount, f.harm_currency
FROM funding f, objects o
WHERE f.id = o.id AND o.obj_type = 26
GROUP BY o.activity_id, f.harm_currency ORDER BY o.activity_id ;

CREATE OR REPLACE VIEW obj_funding_amount_org AS
SELECT DISTINCT o.activity_id, f.org_id, orgd.name, orgd.lang, sum(f.amount) AS amount, f.harm_currency
   FROM funding f, objects o, orgs_desc orgd
  WHERE f.id = o.id AND o.obj_type = 26 AND orgd.id = f.org_id
  GROUP BY o.activity_id, f.org_id, orgd.name, orgd.lang, f.harm_currency
  ORDER BY o.activity_id, f.org_id, orgd.lang, orgd.name, sum(f.amount), f.harm_currency;

 
CREATE OR REPLACE VIEW obj_funding_amount_org_disb AS
SELECT DISTINCT o.activity_id, f.org_id,  orgd.name, orgd.lang, sum(f3.amount) AS amount, f3.currency
FROM funding f, objects o, orgs_desc orgd, fnd3_tables f3
WHERE f.id = o.id AND o.obj_type = 26 AND orgd.id = f.org_id AND f.id=f3.fnd_item_id AND
f3.type='D'
GROUP BY o.activity_id, f.org_id, orgd.name, orgd.lang, f3.currency
ORDER BY o.activity_id, f.org_id, orgd.lang;

CREATE OR REPLACE VIEW obj_funding_amount_org_comm AS
SELECT DISTINCT o.activity_id, f.org_id,  orgd.name, orgd.lang, sum(f3.amount) AS amount, f3.currency
FROM funding f, objects o, orgs_desc orgd, fnd3_tables f3
WHERE f.id = o.id AND o.obj_type = 26 AND orgd.id = f.org_id AND f.id=f3.fnd_item_id AND
f3.type='C'
GROUP BY o.activity_id, f.org_id, orgd.name, orgd.lang, f3.currency
ORDER BY o.activity_id, f.org_id, orgd.lang;


CREATE OR REPLACE VIEW obj_funding_orgs AS
SELECT DISTINCT o.activity_id, org.id, org.name, org.lang
FROM orgs_desc org, orgs_inv oi, objects o
WHERE org.id = oi.org_id AND o.id = oi.id AND o.obj_type = 10 AND oi.role=1
ORDER BY o.activity_id, org.id;

CREATE OR REPLACE VIEW obj_funding_status AS
SELECT DISTINCT o.activity_id, stat.id, stat.name, stat.lang
FROM funding_status stat, funding f, objects o
WHERE stat.id = f.status AND o.id = f.id AND o.obj_type = 26
ORDER BY o.activity_id, stat.id;

CREATE OR REPLACE VIEW obj_implementing_orgs AS
SELECT DISTINCT o.activity_id, org.id, org.name, org.lang
FROM orgs_desc org, orgs_inv oi, objects o
WHERE org.id = oi.org_id AND o.id = oi.id AND o.obj_type = 10 AND oi.role=100
ORDER BY o.activity_id, org.id;

CREATE OR REPLACE VIEW obj_regions AS
SELECT DISTINCT o.activity_id, loc.reg_id, reg.name
FROM regions reg, locations loc, objects o
WHERE reg.id = loc.reg_id AND o.id = loc.id AND o.obj_type = 23
ORDER BY o.activity_id, loc.reg_id;

CREATE OR REPLACE VIEW obj_sectors AS
SELECT o.activity_id, ras.ro_aida_code, ras.name, ras.lang
FROM sectors s, ro_aida_sectors ras, objects o
WHERE ras.ro_aida_code = s.ro_aida_code AND o.id = s.id AND o.obj_type = 20
ORDER BY o.activity_id, ras.ro_aida_code;

CREATE OR REPLACE VIEW obj_titles AS 
SELECT o.activity_id, t.name, t.lang FROM objects o, gen_titles t WHERE
o.id=t.id AND o.obj_type=12 ORDER BY o.activity_id; 

CREATE OR REPLACE VIEW obj_towns AS
SELECT DISTINCT o.activity_id, tw.sirsup_id, tw.name
FROM towns tw, locations loc, objects o
WHERE tw.sirsup_id = loc.sirsup_id AND o.id = loc.id AND o.obj_type = 23
ORDER BY o.activity_id, tw.sirsup_id;

CREATE OR REPLACE VIEW obj_types_assist AS
SELECT DISTINCT o.activity_id, t.id, t.value, t.lang
FROM prop_list_items t, funding f, objects o
WHERE t.id = f.termassist AND o.id = f.id AND o.obj_type = 26 AND t.list_id=2
ORDER BY o.activity_id, t.id;

CREATE OR REPLACE VIEW obj_terms_assist AS
SELECT DISTINCT o.activity_id, t.id, t.value, t.lang
FROM prop_list_items t, funding f, objects o
WHERE t.id = f.termassist AND o.id = f.id AND o.obj_type = 26 AND t.list_id=3
ORDER BY o.activity_id, t.id;

-----------------------------------------------
--search results format
--
-- TOC entry 2 (OID 536456)
-- Name: result_formats; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE result_formats (
    id serial NOT NULL,
    name character varying(512),
    format character varying(1024),
    selected character(1) DEFAULT ('N'),
    prim character(1) DEFAULT ('N'),
    user_id integer REFERENCES users(id)
);

INSERT INTO result_formats (name,format,selected,prim) VALUES ('Standard','#title#sectors#countries','N','Y');


-----------------------------------------------
--prj_country_name function

CREATE OR REPLACE FUNCTION prj_country_name(bigint) RETURNS varchar
AS '
DECLARE
       act_id ALIAS FOR $1;
       ct integer;
       ret varchar;

BEGIN
        ct:=lpd_get_prj_obj_count(23,act_id);
        IF ct = 0 THEN
                RETURN ''-'';
        END IF;

        IF ct = 1 THEN
                SELECT INTO ret cnt.name FROM countries_iso3 cnt,
                locations l2, objects o2 WHERE l2.id = o2.id
                AND cnt.iso3 = l2.country_iso3 AND o2.activity_id = act_id
                AND o2.obj_type=23;
                RETURN ret;
        END IF;

        RETURN ''--multiple--'';
END;
' LANGUAGE plpgsql;

-----------------------------------------------
--prj object count
--
-- TOC entry 154 (OID 509056)
-- Name: lpd_get_prj_obj_count(integer, bigint); Type: FUNCTION; Schema: public; Owner: lpd
--

CREATE OR REPLACE FUNCTION lpd_get_prj_obj_count(integer, bigint) RETURNS integer
    AS '
--version 0.1
--created by Postelnicu Mihai (mihai@code.ro)
DECLARE
       type_id ALIAS FOR $1;
       act_id ALIAS FOR $2;
        cnt integer;

BEGIN
     cnt:= COUNT(*) FROM objects WHERE obj_type=type_id AND activity_id=act_id;
     RETURN cnt;
END;
'
    LANGUAGE plpgsql;
    
-----------------------------------------------    
--datasource institutions
update system_properties set value=value||'\np.sourceinst=0:2' WHERE name='actoobjoptions';    

-----------------------------------------------    
--activity note fields

update system_properties set value=value||'\np.notefields=0:2' WHERE name='actoobjoptions';
CREATE TABLE gen_notefields1 (
	id 	INTEGER NOT NULL,
	name 	VARCHAR(3000) NOT NULL,
	lang 	CHAR(2),
	CONSTRAINT nf1_2obj FOREIGN KEY(id) REFERENCES objects(id)
);
CREATE TABLE gen_notefields2 (
	id 	INTEGER NOT NULL,
	name 	VARCHAR(3000) NOT NULL,
	lang 	CHAR(2),
	CONSTRAINT nf2_2obj FOREIGN KEY(id) REFERENCES objects(id)
);
CREATE TABLE gen_notefields3 (
	id 	INTEGER NOT NULL,
	name 	VARCHAR(3000) NOT NULL,
	lang 	CHAR(2),
	CONSTRAINT nf3_2obj FOREIGN KEY(id) REFERENCES objects(id)
);
CREATE TABLE gen_notefields4 (
	id 	INTEGER NOT NULL,
	name 	VARCHAR(3000) NOT NULL,
	lang 	CHAR(2),
	CONSTRAINT nf4_2obj FOREIGN KEY(id) REFERENCES objects(id)
);


    
-----------------------------------------------    
-- harvest locations bug

DROP TABLE trn_harvest_locations;
DROP SEQUENCE trn_harvest_seq;

CREATE SEQUENCE trn_harvest_seq
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


CREATE TABLE trn_harvest_locations (
    id integer DEFAULT nextval('trn_harvest_seq'::text) NOT NULL,
    name character varying(255),
    baseurl character varying(512),
    lang character(2),
    performed timestamp
) WITHOUT OIDS;


--add some harvesters
insert into trn_harvest_locations (name,baseurl,lang) values ('Romanian LPD','http://lpd.ro-gateway.ro','en');
insert into trn_harvest_locations (name,baseurl,lang) values ('Romanian LPD','http://lpd.ro-gateway.ro','ro');
insert into trn_harvest_locations (name,baseurl,lang) values ('Polish LPD','http://lpd.pldg.pl/lpd/','pl');
insert into trn_harvest_locations (name,baseurl,lang) values ('Moroccan LPD','http://194.204.200.105:8080/lpd/','fr');



    
-----------------------------------------------    
--new breadcrumbs
DROP TABLE breadcrumbs;
--
-- TOC entry 2 (OID 506290)
-- Name: breadcrumbs; Type: TABLE; Schema: public; Owner: lpd
--

CREATE TABLE breadcrumbs (
    url character varying(255),
    label character varying(128),
    parentlabel character varying(128)
) WITHOUT OIDS;


--
-- Data for TOC entry 4 (OID 506290)
-- Name: breadcrumbs; Type: TABLE DATA; Schema: public; Owner: lpd
--

COPY breadcrumbs (url, label, parentlabel) FROM stdin;
/translator/edit_item.do?action=new	bc.translator	bc.admin
/viewgroups.do	bc.groups.view	bc.admin
\N	bc.groups.edit	bc.groups.view
/categories.do?mode=list	bc.categories.view	bc.admin
\N	bc.categories.edit	bc.categories.view
\N	bc.groups.confirmdelete	bc.groups.view
/sectors.do	bc.sectors.browser	bc.admin
/systemProperties.do	bc.props	bc.admin
\N	bc.users.edit	bc.users.list
/editActivity?page=2	bc.project.page2	bc.project.page1
/editActivity?page=3	bc.project.page3	bc.project.page2
/editActivity?page=4	bc.project.page4	bc.project.page3
/editActivity?page=6	bc.project.page6	bc.project.page5
/editActivity?page=5	bc.project.page5	bc.project.page4
\N	bc.project.page7	bc.project.page6
/editActivity?page=1	bc.project.page1	bc.desktop
/desktop.do?mode=firstlist	bc.project.list	bc.desktop
/approve.do	bc.approval.list	bc.desktop
/alert.do	bc.alert.list	bc.desktop
\N	bc.alert.view	bc.alert.list
/bookmark.do	bc.bookmarks	bc.desktop
\N	bc.prjsearch.results	bc.prjsearch.form
\N	bc.approval.changes	bc.approval.list
\N	bc.alerter.edit	bc.alerter.list
\N	bc.alerter.view	bc.alerter.list
/prjfilter.do?mode=list	bc.prjfilter.list	bc.alerter.list
/aoof.do	bc.objconf	bc.admin
/admin/listorgs.do	bc.orgexp	bc.admin
/admin/listusers.do	bc.userexp	bc.admin
/admin/listpersons.do	bc.persexp	bc.admin
/alerter.do?mode=list	bc.alerter.list	bc.desktop
\N	bc.prjfilter.edit	bc.prjfilter.list
/desktop.do?	bc.desktop	bc.home
/?	bc.home	bc._nothing_
/location/editor.do	bc.locman	bc.admin
\N	bc.prjview	bc.prjsearch.form
\N	bc.prjmail	bc.prjview
\N	bc.edittag	bc.admin
/usr/users.jsp?	bc.users.list	bc.admin
/admin.jsp?	bc.admin	bc.home
/vieworg.do?	bc.org.list	bc.desktop
/viewpers.do?	bc.person.list	bc.desktop
/orgpers/search.do?	bc.orgpers	bc.home
\N	bc.orgpers.orglist	bc.orgpers
\N	bc.orgpers.personlist	bc.orgpers
/admin/org/quickadd.do	bc.quickaddman	bc.admin
\N	bc.quickaddedit	bc.quickaddman
/translator/exchange.do	bc.te.list	bc.admin
\N	bc.te.edit	bc.te.list
\N	bc.te.result	bc.te.list
/search.do?newsearch	bc.prjsearch.form	bc.home
/resultsFormat.do?mode=list	bc.prjsearch.format.list	bc.prjsearch.form
\N	bc.prjsearch.format.edit	bc.prjsearch.format.list
\.


--
-- TOC entry 3 (OID 506292)
-- Name: breadcrumbs_pk; Type: CONSTRAINT; Schema: public; Owner: lpd
--

ALTER TABLE ONLY breadcrumbs
    ADD CONSTRAINT breadcrumbs_pk UNIQUE (label);


